﻿

------------------------------------------------------------------------------------------------------------
--分页存储过程
                   Create PROCEDURE [dbo].[sp_GetListByPageAndFileds]
                    ( 
                        @pageSize  int,				/**  每页数据量        **/   
                        @currentPage int = 1,		/**  当前页，默认为为1 **/       
                        @fields   varchar(2000),	/**  查询字段，可以用 * 表示所有 **/     
                        @tablename  varchar(max),   /**  表名，或者为查询得出的 子表 ，子表查询sql需要括号括起来，并指定新表名  **/     
                        @orderString varchar(1000), /**  排序字段 + asc/desc   **/       
                        @whereString varchar(1000)  /**  不包含‘where’的字符串 **/      
                    )
                        AS
                        BEGIN
                         DECLARE @sql varchar(2000)
                         DECLARE @strOrder varchar(2000)
                         DECLARE @strWhere varchar(2000)
                         declare @recordcount int 
                         declare @convertorderstr varchar(2000)
                        declare @countsql nvarchar(4000)
                        declare @totalpage int
                         set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','')
                         if @strOrder != ''
set @strOrder = ' order by ' + @strOrder
else
                          set @strOrder = ' order by ID DESC'
set @strOrder=lower(@strOrder)
set @convertorderstr=replace(@strOrder,'desc','d_e_s_c')
set @convertorderstr=replace(@convertorderstr,'asc','desc')
set @convertorderstr=replace(@convertorderstr,'d_e_s_c','asc')
set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','')
if @strWhere != ''
    set @strWhere = ' where ' + @strWhere

set @countsql='select @a=count(*) from ' + @tablename + @strWhere
exec  sp_executesql @countsql,N'@a int output',@recordcount output 


if @pageSize = 0
    set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder
else
                         begin
if @recordcount%@pageSize=0
    set @totalpage=@recordcount/@pageSize
else
                                    set @totalpage=@recordcount/@pageSize+1
if @totalpage <=1
    set @currentPage=1 
if @totalpage <@currentPage 
    set @currentPage=@totalpage
if @currentPage = 1
    set @sql = 'select top ' + Str(@pageSize)+' '+ @fields + ' from ' + @tablename + @strWhere + @strOrder
else
                            if (@currentPage - 1) * @pageSize > @recordcount / 2
set @sql = 'select top ' + str(@pageSize) + ' * from (select top ' + str((@recordcount - (@currentPage - 1) * @pageSize)) + ' ' + @fields + ' from ' + @tablename + @strWhere + @convertorderstr + ') as t1  ' + @strOrder
else
                                set @sql = 'select * from(select top ' + str(@pageSize) + ' * from (select top ' + str(@pageSize * @currentPage) + ' ' + @fields + ' from ' + @tablename + @strWhere + @strOrder + ') as t1  ' + @convertorderstr + ') as t2  ' + @strOrder
end
set @sql = @sql + '; select '+str(@recordcount)+' as cnt'
exec(@sql)
END

------------------------------------------------------------------------------------------------------------
--查询存储过程

select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b 
where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V','AF') 
order by a.[name] ASC



